﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Xml;
using System.Web;

namespace ReportService
{
    /// <summary>
    /// Summary description for AverageProductLifecycle
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class AverageProductLifecycle : AbstractWebservice.AbstractService
    {
        [WebMethod]
        public override DataSet getDataset(DataSet dataSet)
        {
            Dictionary<string, string> productLinesDictionary = new Dictionary<string, string>();

            productLinesDictionary.Add("Parts"," ");
            productLinesDictionary.Add("Mountain", "M ");
            productLinesDictionary.Add("Race","R " );
            productLinesDictionary.Add("Sport","S ");
            productLinesDictionary.Add("Touring","T ");

            DataTable dataTable = dataSet.Tables[2];
            
            String strfromMonth = dataTable.Rows[0][dataTable.Columns[0]].ToString();
            String strfromYear = dataTable.Rows[1][dataTable.Columns[0]].ToString();
            String strtoMonth = dataTable.Rows[2][dataTable.Columns[0]].ToString();
            String strtoYear = dataTable.Rows[3][dataTable.Columns[0]].ToString();
            String productLine = dataTable.Rows[4][dataTable.Columns[0]].ToString();

            String fromDate = this.makeDate(strfromMonth, strfromYear);
            String toDate = this.makeDate(strtoMonth, strtoYear);

            String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\AWC\\AWCData.mdf;" + "Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection conn = new SqlConnection();
            SqlDataAdapter adapter = new SqlDataAdapter();

            DataSet ds = new DataSet();

            String productLifeTimeQuery = "SELECT Production.Product.ProductLine, "
                                        + "Production.Product.SellStartDate, "
                                        + "Production.Product.SellEndDate, "
                                        + "Purchasing.PurchaseOrderDetail.StockedQty, "
                                        + "Purchasing.PurchaseOrderHeader.OrderDate, "
                                        + "Production.ProductModel.Name AS ModelName, "
                                        + "Production.Product.Name AS ProductName "
                                        + "FROM Production.Product "
                                        + "INNER JOIN Purchasing.PurchaseOrderDetail ON Production.Product.ProductID = Purchasing.PurchaseOrderDetail.ProductID "
                                        + "INNER JOIN Purchasing.PurchaseOrderHeader ON Purchasing.PurchaseOrderDetail.PurchaseOrderID = Purchasing.PurchaseOrderHeader.PurchaseOrderID "
                                        + "INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID ";
            if (productLinesDictionary[productLine].ToString() != " ")
            {

                productLifeTimeQuery += "WHERE Production.Product.ProductLine = '" + productLinesDictionary[productLine].ToString() + "' ";
            }
            else
            {
                productLifeTimeQuery += "WHERE Production.Product.ProductLine IS NULL ";
            }

            if (fromDate != " ")
                productLifeTimeQuery  += "AND Production.Product.SellStartDate >= '" + fromDate + "' ";
            if (toDate != " ")
            productLifeTimeQuery +=  "AND Production.Product.SellEndDate <= '" + toDate + "' ";
         
                                        productLifeTimeQuery    +=  "ORDER BY ProductName";

                                 
           
            conn.ConnectionString = connectionString;
            adapter = new SqlDataAdapter(productLifeTimeQuery, conn);
            adapter.Fill(ds);
            conn.Close();
            return ds;
        }

        private String makeDate(String month, String year)
        {
         
              String date = " ";

            if (month != "" && year != "")
            {
                 date = year + "-" + month + "-" + "01";
            }
         

            return date;
        }

        private void addComponent(XmlDocument xmlDoc, XmlElement elementComponents, String labelText, 
                                    String typeText)
        {
            Dictionary<string, string> productLinesDictionary = new Dictionary<string, string>();

            productLinesDictionary.Add("", "Parts");
            productLinesDictionary.Add("M", "Mountain");
            productLinesDictionary.Add("R", "Race");
            productLinesDictionary.Add("S", "Sport");
            productLinesDictionary.Add("T", "Touring");

            /////add the first component
            //Add Component to Components
            XmlElement elementComponent = xmlDoc.CreateElement("", "component", "");
            elementComponents.AppendChild(elementComponent);

            //add type to component
            XmlAttribute label = xmlDoc.CreateAttribute("label");
            label.Value = labelText;
            elementComponent.Attributes.Append(label);

            //add type to component
            XmlAttribute type = xmlDoc.CreateAttribute("type");
            type.Value = typeText;
            elementComponent.Attributes.Append(type);

            if (typeText == "list")
            {
                String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\AWC\\AWCData.mdf;" + "Integrated Security=True;Connect Timeout=30;User Instance=True";
                
                String productLineQuery = "SELECT DISTINCT ProductLine FROM Production.Product";

                DataSet ds = new DataSet();
                SelectRows(ds, connectionString, productLineQuery);
                int rowCount = ds.Tables[0].Rows.Count;

                for (int i = 0; i < rowCount; i++)
                {
                    XmlElement item = xmlDoc.CreateElement("item");
                    elementComponent.AppendChild(item);

                    XmlAttribute name = xmlDoc.CreateAttribute("name");
                    name.Value = i.ToString();
                    XmlAttribute value = xmlDoc.CreateAttribute("value");

                    String ProductLine = ds.Tables[0].Rows[i]["ProductLine"].ToString();

                    value.Value = productLinesDictionary[ds.Tables[0].Rows[i]["ProductLine"].ToString().Trim()];

                    item.Attributes.Append(name);
                    item.Attributes.Append(value);
                }
            }
        }


        [WebMethod]
        public override DataSet getComponents()
        {
            XmlDocument xmlDoc = new XmlDocument();
            XmlNode xmlnode = xmlDoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
            xmlDoc.AppendChild(xmlnode);

            //Add Components(root) to Xml
            XmlElement elementComponents = xmlDoc.CreateElement("", "components", "");
            xmlDoc.AppendChild(elementComponents);

            //add the first component
            addComponent(xmlDoc, elementComponents, "Select from-month", "string");
            addComponent(xmlDoc,elementComponents, "Select from-year", "string");
            //add the second component
            addComponent(xmlDoc, elementComponents, "Select to-month", "string");
            addComponent(xmlDoc, elementComponents, "Select to-year", "string");
            //add the third component
            addComponent(xmlDoc, elementComponents, "productline list", "list");

            //debug test
                try
                {
                    xmlDoc.Save("D:\\components.xml");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            //debug test

            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xmlDoc));

            return ds;
        }

        //function to execute a query and return it as a dataset
        public DataSet SelectRows(DataSet dataset,
        string connectionString, string queryString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(queryString, connection);
                adapter.Fill(dataset);
                return dataset;
            }
        }
    }
}